﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Deployment.Application;
using System.Collections;

namespace QLWeb_XDPM.Data
{
    class KetNoi
    {
        string strconnect = "Data Source=HANHLUONG-PC\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\BANTRANGSUC.mdf;Integrated Security=True";
        SqlConnection sqlcon;
        SqlCommand sqlcom;
        SqlDataAdapter sqlda;
        SqlDataReader sqldr;        
        DataSet ds = new DataSet();

        #region ket noi
        void ketnoi()
        {
            sqlcon = new SqlConnection(strconnect);
            sqlcon.Open();
        }
        #endregion

        #region ngat ket noi
        void ngatketnoi()
        {
            sqlcon.Close();
        }
        #endregion

        #region thuc thi ket noi
        public void thucthiketnoi(string strsql)
        {
            
            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqlcom.ExecuteNonQuery();
            ngatketnoi();
        }
        #endregion

        #region kiem tra
        public bool kiemtra(string dauvao, string strsql, byte chiso)
        {
            bool kt = false;
            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr[chiso].ToString().ToLower() == dauvao.ToLower())
                    kt = true;
            }
            ngatketnoi();
            return kt;
        }
        #endregion

        #region do du lieu va datagridview

        public void loaddatagridview(DataGridView dg, string strselect)
        {
            ds.Clear();
            sqlda = new SqlDataAdapter(strselect, strconnect);
            sqlda.Fill(ds, "tenbang");
            dg.DataSource = ds.Tables[0];
        }
        #endregion

        #region load com bobox
        public void loadcombobox(ComboBox cb, string strselect, byte chiso)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Items.Add(sqldr[chiso].ToString());
            }
            ngatketnoi();
        }
        #endregion

        #region load textbox
        public void loadtextbox(TextBox cb, string strselect)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Text = sqldr[0].ToString();
            }
            ngatketnoi();
        }
        #endregion

        #region load date time
        public void loaddatetime(DateTimePicker cb, string strselect, byte chiso)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Text = sqldr[chiso].ToString();
            }
            ngatketnoi();
        }
        #endregion

        #region load textbox chi so
        public void loadtextboxchiso(TextBox cb, string strselect, byte chiso)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Text = sqldr[chiso].ToString();
            }
            ngatketnoi();
        }
        #endregion

        #region get data
        public string getdata(string dauvao, string strsql)
        {
            string strketqua = "";
            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr[0].ToString().ToLower() == dauvao.ToLower())
                    strketqua = sqldr[1].ToString();
            }
            ngatketnoi();
            return strketqua;
        }
        #endregion

        #region "Xay dung phuong thuc thuc hien cac cau truy van insert, update, delete..."
        public bool ThucHienCauTruyVan(string truyvan, IDictionary<string, string> d)
        {
            try
            {
                if (sqlcon.State == ConnectionState.Closed)
                    sqlcon.Open();
                SqlCommand cmd = new SqlCommand(truyvan, sqlcon);
                if (d != null)
                {
                    IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                    while (en.MoveNext())
                    {
                       SqlParameter param = new SqlParameter();
                        param.ParameterName = en.Key.ToString();
                        param.Value = en.Value.ToString();
                        cmd.Parameters.Add(param);
                    }
                }
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    sqlcon.Close();
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion
    }


}
